package model;

import java.util.Vector;

import lib.Bill;
import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.DatabaseSecurityOptions;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;

public class DB 
{
	private final String DBFILE = "billtracker.db";
	public static final String DBLOCATION = "file:///SDCard/Databases/voiddigits/";
	private URI myURI;
	private Database db;
	public static final String[] DEFAULT_BILLCATEGORIES = {"Phone", "Electric", "Credit Card", };
	public static final String CURRENCY = "$";
	
	public DB()
	{
		createDB();
	}
	
	private void createDB()
	{
		try
		{		
		   myURI = URI.create( DBLOCATION + DBFILE );
		   
		   if(!DatabaseFactory.exists(myURI))
		   {
			   db = DatabaseFactory.openOrCreate(myURI,new DatabaseSecurityOptions(false));
			   
			   createCategoryTable();
			   createBillOwingTable();
			   createBillPaidTable();
			   createBillsTable();
			   
			   closeDB();
		   }
		}
		catch ( Exception e )
		{
		   System.out.println( e.getMessage() );
		   e.printStackTrace();
		}
	}
	private void openDB()
	{
		try{
			
			db = DatabaseFactory.openOrCreate(myURI);
			System.out.println("DB IS OPEN");

		}catch(Exception e)
		{
			System.out.println("Error Opening DB:: " +e.toString());
		}
	}
	private void closeDB() throws Exception
	{
		if(db != null)
		{
			db.close();
			System.out.println("DB IS CLOSED");
		}
	}
	
	private void createLocationTable()
	{
		String tblLocation = "CREATE TABLE Location(locationID INTEGER PRIMARY KEY," +
				"country TEXT, province TEXT, city TEXT)";
		try{
			//openDB();
		   Statement st = db.createStatement( tblLocation );
		   st.prepare();
		   st.execute();
		   st.close();
		   //closeDB();
		}catch(Exception e)
		   {
			   
		   }
	}
	
	private void createProfileTable()
	{
		String tblProfile = "CREATE TABLE IF NOT EXISTS Profile(id INTEGER primary key, " +
				"pin TEXT, screenname TEXT, dob TEXT, status TEXT, aboutMe TEXT, " +
				"gender TEXT, searchable TEXT , picture BLOB)";
		try{
			//openDB();
			Statement st = db.createStatement(tblProfile);
			st.prepare();
            st.execute();
            st.close();

		}catch(Exception e)
		{
			System.out.println("Error creating Profile table::: "+e.getMessage());
		}
	}
	
	private void createCategoryTable()
	{
		String tblCategory = "CREATE TABLE IF NOT EXISTS BillCategory(id INTEGER primary key, " +
				"CategoryName TEXT)";
		try{
			//openDB();
			Statement st = db.createStatement(tblCategory);
			st.prepare();
            st.execute();
            
            for(int i=0; i < DEFAULT_BILLCATEGORIES.length; i++)
            {
            	st = db.createStatement("INSERT INTO BillCategory(categoryName) Values( "+ DEFAULT_BILLCATEGORIES[i] +" )");
            	st.prepare();
                st.execute();
            }
                        
            st.close();
		}catch(Exception e)
		{
			System.out.println("Error creating Profile table::: "+e.getMessage());
		}
	}
	
	public void insert(String sql)
	{
		try{
			openDB();
			Statement st = db.createStatement(sql);// Statement();
			st.prepare();
            st.execute();
            st.close();
            closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	
	private void createBillOwingTable()
	{
		String tblBillsPaid = "CREATE TABLE IF NOT EXISTS BillsOwing(id INTEGER primary key, " +
		"BillID INTEGER, date TEXT, amount TEXT)";
		
		try
		{
			openDB();
			Statement st = db.createStatement(tblBillsPaid);
			st.prepare();
            st.execute();
            st.close();
            
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void createBillPaidTable()
	{
		String tblBillsPaid = "CREATE TABLE IF NOT EXISTS BillsPaid(id INTEGER primary key, " +
		"BillID INTEGER, date TEXT, amount TEXT)";
		
		try
		{
			openDB();
			Statement st = db.createStatement(tblBillsPaid);
			st.prepare();
            st.execute();
            st.close();
            
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void createBillsTable()
	{
		String tblBills = "CREATE TABLE IF NOT EXISTS Bills(id INTEGER primary key, " +
		"CategoryID INTEGER, Name TEXT, Company TEXT, Type TEXT)";
		
		try
		{
			openDB();
			
			Statement st = db.createStatement(tblBills);
			st.prepare();
            st.execute();
            
            st.close();
            
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public String getCategory(int categoryID)
	{
		String category = "";
		try{
			openDB();
			
			Statement st = db.createStatement("SELECT CategoryName from BillCategory WHERE id="+categoryID);
			st.prepare();
            st.execute();
            
            Cursor c = st.getCursor();
            
            if(!c.isEmpty())
            {
            	c.first();
	            Row r = c.getRow();
	            category  = r.getString(1);
            }                      
            closeDB();			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return category;		
	}
	
	public Vector getBills()
	{
		Vector bills = new Vector();
		try
		{
			openDB();
			
			Statement st = db.createStatement("SELECT Bills.id, Name, Company, Type, CategoryName FROM Bills B, BillCategory BC " +
					"WHERE B.CategoryID=BC.id");
			st.prepare();
            st.execute();
            
            Cursor c = st.getCursor();
            if(!c.isEmpty())
            {
            	c.first();
            	Row r = c.getRow();
            	Bill bill = new Bill(r.getString(5), r.getString(4), r.getString(3), r.getString(2));
            	bill.setBillID(r.getInteger(1));
            	bills.addElement(bill);
            	
            	while(c.next())
	            {
	            	r = c.getRow();
	            	bill = new Bill(r.getString(5), r.getString(4), r.getString(3), r.getString(2));
	            	bill.setBillID(r.getInteger(1));
	            	bills.addElement(bill);
	            }
            }
            
            closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return bills;
	}
	
	public String[] getCategories()
	{
		Vector catNames = new Vector();
		try{
			openDB();
			Statement st = db.createStatement("SELECT CategoryName from BillCategory");
			st.prepare();
            st.execute();
            Cursor c = st.getCursor();
            
            if(!c.isEmpty())
            {
            	
            	c.first();
	            Row r = c.getRow();
	            catNames.addElement(r.getString(1));
	            while(c.next())
	            {
	            	catNames.addElement(c.getRow().getString(1));
	            }
            }            
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		String[] obj = new String[catNames.size()];
        
        for(int i=0; i < catNames.size(); i++)
        {
        	obj[i] = (String)catNames.elementAt(i);
        }
        
        return obj;

	}
	
	
	/*
	public void insertProfileIntoDB(Profile p)
	{
		try{
			openDB();
			
			Statement st = db.createStatement("DELETE from Profile");
			st.prepare();
			st.execute();
			st.close();
			
			String sql = "UPDATE PROFILE SET 'screenname'='"+p.getScreenname()+"', " +
					"'dob'='"+p.getDOB().getTime()+"', 'status'='"+p.getStatus()+"', 'aboutMe' ='"+p.getAboutme()+"'," +
					"'gender'='"+p.getGender()+"', 'searchable' ='"+(p.isPrivate()? "false": "true")+"'"+
					 " WHERE 'pin'='"+DeviceInfo.getDeviceId()+"'";
			sql = "INSERT INTO PROFILE(pin,screenname,dob,status,aboutMe,gender,searchable) " +
					"VALUES('"+DeviceInfo.getDeviceId()+"','"+p.getScreenname()+"','"+p.getDOB().getTime()+"'," +
							"'"+p.getStatus()+"','"+p.getAboutme()+"','"+p.getGender()+"', '"+(p.isPrivate()? "false": "true")+"')";
			
			sql = "INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)";
			//db.executeStatement(sql+";");
			byte[] b = p.getImage().getData();
			
			st = db.createStatement("INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)");
			st.prepare();
			st.bind(1, DeviceInfo.getDeviceId());
			st.bind(2, p.getScreenname());
			st.bind(3, p.getDOB().getTime());
			st.bind(4, p.getStatus());
			st.bind(5, p.getAboutme());
			st.bind(6, p.getGender());
			st.bind(7, (p.isPrivate()? "false": "true"));
			st.bind(8, b);
			//st.bind(1, p.getImage().getData());
            st.execute();
            st.close();
        
			closeDB();
		}catch(Exception e)
		{
			System.out.println("ERROR in UPDATE DB:: " + e.getMessage() + " :: " + e.toString());
		}
	}
	
	public void updateDB(Profile p)
	{
		try{
			openDB();
			
			String sql = "UPDATE PROFILE SET 'screenname'='"+p.getScreenname()+"', " +
					"'dob'='"+p.getDOB().getTime()+"', 'status'='"+p.getStatus()+"', 'aboutMe' ='"+p.getAboutme()+"'," +
					"'gender'='"+p.getGender()+"', 'searchable' ='"+(p.isPrivate()? "false": "true")+"'"+
					 " WHERE 'pin'='"+DeviceInfo.getDeviceId()+"'";
			sql = "INSERT INTO PROFILE(pin,screenname,dob,status,aboutMe,gender,searchable) " +
					"VALUES('"+DeviceInfo.getDeviceId()+"','"+p.getScreenname()+"','"+p.getDOB().getTime()+"'," +
							"'"+p.getStatus()+"','"+p.getAboutme()+"','"+p.getGender()+"', '"+(p.isPrivate()? "false": "true")+"')";
			
			sql = "INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)";
			//db.executeStatement(sql+";");
			byte[] b = p.getImage().getData();
			
			Statement st = db.createStatement("INSERT INTO Profile VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)");
			st.prepare();
			st.bind(1, DeviceInfo.getDeviceId());
			st.bind(2, p.getScreenname());
			st.bind(3, p.getDOB().getTime());
			st.bind(4, p.getStatus());
			st.bind(5, p.getAboutme());
			st.bind(6, p.getGender());
			st.bind(7, (p.isPrivate()? "false": "true"));
			st.bind(8, b);
			//st.bind(1, p.getImage().getData());
            st.execute();
            
            st.close();
            
			closeDB();
		}catch(Exception e)
		{
			System.out.println("ERROR in UPDATE DB:: " + e.getMessage() + " :: " + e.toString());
		}
	}
*/
	public void login(String username, String pass, boolean remember) 
	{
		try{
			openDB();
				Statement st = db.createStatement("DELETE FROM Login");
	            st.prepare();
	            st.execute();
	            
	            st = db.createStatement("INSERT INTO Login VALUES(?, ?, ?)");
	            st.prepare();
				st.bind(1, username);
				st.bind(2, pass);
				st.bind(3, remember);
				st.execute();
			closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public boolean rememberLogin() 
	{
		try{
			openDB();
				Statement st = db.createStatement("SELECT username, password, remember FROM Login Where remember=true");
	            st.prepare();
	            st.execute();
	            
	            Cursor c = st.getCursor();
	            if(!c.isEmpty())
	            {
	            	c.first();
		            Row r = c.getRow();
		            return (boolean)r.getBoolean(2);
	            }
				st.execute();
			closeDB();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
}
